using AuthService.Domain.Entities;
using AuthService.Domain.Repositories;
using AuthService.Infrastructure.DTOs;
using Dapper;
using Microsoft.Extensions.Logging;

namespace AuthService.Infrastructure.Repositories;

/// <summary>
/// UserRepository的统计和查询扩展方法
/// </summary>
public partial class UserRepository
{
    /// <summary>
    /// 获取用户统计信息
    /// </summary>
    public async Task<UserStatistics> GetStatisticsAsync(string? tenantId = null, CancellationToken cancellationToken = default)
    {
        var sql = @"
            SELECT
                COUNT(*) as TotalUsers,
                COUNT(*) FILTER (WHERE ""IsActive"" = true) as ActiveUsers,
                COUNT(*) FILTER (WHERE ""IsLocked"" = true) as LockedUsers,
                COUNT(*) FILTER (WHERE ""IsEmailVerified"" = true) as EmailVerifiedUsers,
                COUNT(*) FILTER (WHERE ""IsPhoneVerified"" = true) as PhoneVerifiedUsers,
                COUNT(*) FILTER (WHERE ""CreatedAt"" >= @Today) as TodayNewUsers,
                COUNT(*) FILTER (WHERE ""CreatedAt"" >= @WeekStart) as WeekNewUsers,
                COUNT(*) FILTER (WHERE ""CreatedAt"" >= @MonthStart) as MonthNewUsers,
                COUNT(*) FILTER (WHERE ""LastLoginAt"" >= @Last30Days) as ActiveUsersLast30Days
            FROM ""Users""
            WHERE ""IsDeleted"" = false";

        var today = DateTime.UtcNow.Date;
        var weekStart = DateTime.UtcNow.Date.AddDays(-(int)DateTime.UtcNow.DayOfWeek);
        var monthStart = new DateTime(DateTime.UtcNow.Year, DateTime.UtcNow.Month, 1);
        var last30Days = DateTime.UtcNow.AddDays(-30);

        object parameters;

        if (!string.IsNullOrWhiteSpace(tenantId))
        {
            sql += " AND TenantId = @TenantId";
            parameters = new
            {
                Today = today,
                WeekStart = weekStart,
                MonthStart = monthStart,
                Last30Days = last30Days,
                TenantId = tenantId
            };
        }
        else
        {
            parameters = new
            {
                Today = today,
                WeekStart = weekStart,
                MonthStart = monthStart,
                Last30Days = last30Days
            };
        }

        try
        {
            using var connection = CreateConnection();
            return await connection.QuerySingleAsync<UserStatistics>(sql, parameters);
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, "获取用户统计信息失败");
            throw;
        }
    }

    /// <summary>
    /// 获取最近登录的用户
    /// </summary>
    public async Task<IEnumerable<User>> GetRecentlyLoggedInAsync(int count = 10, string? tenantId = null, CancellationToken cancellationToken = default)
    {
        var sql = @"
            SELECT * FROM ""Users""
            WHERE ""IsDeleted"" = false
            AND ""IsActive"" = true
            AND ""LastLoginAt"" IS NOT NULL";

        if (!string.IsNullOrWhiteSpace(tenantId))
        {
            sql += " AND \"TenantId\" = @TenantId";
        }

        sql += @"
            ORDER BY ""LastLoginAt"" DESC
            LIMIT @Count";

        try
        {
            using var connection = CreateConnection();
            var userDtos = await connection.QueryAsync<UserDto>(sql, new { Count = count, TenantId = tenantId });
            return userDtos.Select(dto => dto.ToEntity());
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, "获取最近登录用户失败");
            throw;
        }
    }

    /// <summary>
    /// 获取锁定的用户
    /// </summary>
    public async Task<IEnumerable<User>> GetLockedUsersAsync(string? tenantId = null, CancellationToken cancellationToken = default)
    {
        var sql = @"
            SELECT * FROM ""Users""
            WHERE ""IsDeleted"" = false
            AND ""IsLocked"" = true";

        if (!string.IsNullOrWhiteSpace(tenantId))
        {
            sql += " AND \"TenantId\" = @TenantId";
        }

        sql += " ORDER BY \"LockoutEnd\" DESC";

        try
        {
            using var connection = CreateConnection();
            var userDtos = await connection.QueryAsync<UserDto>(sql, new { TenantId = tenantId });
            return userDtos.Select(dto => dto.ToEntity());
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, "获取锁定用户列表失败");
            throw;
        }
    }
}
